<?php

class DbChangeScriptsController extends MyController
{
    public $name = 'DBChangeScriptsController';
    public function actionIndex()
    {
        $this->render('index',array('files'=>$this->_getFiles()));
        

    }
    public function actionViewLog()
    {
        $log = Yii::app()->basePath.'/../sql/'.$_GET['script'].'.log';
        if(!file_exists($log)) {
            Yii::app()->user->setFlash('error',sprintf(_("The script log file <em>%s</em> doesn't exist"),basename($log)));
        } else {
            $parser = new CMarkdownParser;
            $logContents = $parser->safeTransform(file_get_contents($log));
        }
        $this->render('index',array('files'=>$this->_getFiles(),'logFile'=> basename($log), 'logContents'=>$logContents));
    }
    public function actionView()
    {
        $script = Yii::app()->basePath.'/../sql/'.$_GET['script'];
        if(!file_exists($script)) {
            Yii::app()->user->setFlash('error',sprintf(_("The script file <em>%s</em> doesn't exist"),basename($script)));
        } else {
            $parser = new CMarkdownParser;
            $scriptContents = $parser->safeTransform("~~~\n[mysql showLineNumbers=1]\n".file_get_contents($script)."\n~~~");
        }
        $this->render('index',array('files'=>$this->_getFiles(),'scriptFile'=> $_GET['script'], 'scriptContents'=>$scriptContents));
    }
    public function actionRun()
    {
        $script = Yii::app()->basePath.'/../sql/'.$_GET['script'];
        if(!file_exists($script)) {
            Yii::app()->user->setFlash('error',sprintf(_("The script file <em>%s</em> doesn't exist"),basename($script)));
        } else {
            $sql = file_get_contents($script);
            // Convert linebreaks to UNIX
            $sql = str_replace("\r\n", "\n", $sql);
            $sql = str_replace("\r", "\n", $sql);
            $obj = new SqlSplitter;
            $queries = $obj->getQueries($sql);
            $errors = array(); $results = array();
            foreach($queries as $n=>$query) {
                $command = Yii::app()->db->createCommand($query);
                $error = false;
                try {    
                    if(preg_match("/SELECT\s*/",$query)) {
                        $result = $command->query()->rowCount;
                    } else {
                        $result = $command->execute();
                    }
                } catch(Exception $e) {
                    $errors[$n+1] = sprintf(
                        _('<b>Error:</b> %s'),
                        nl2br($e->getMessage())
                    );
                    $error = true;
                }
                if($error==false) {
                    $results[$n+1] = $result;
                }
            }
            // write log file
            $fp = fopen($script.'.log',"wb");
            fwrite($fp, "### ".sprintf(_('DB Change Script: <em>%s</em>'),$_GET['script'])."\n");
            foreach($queries as $n=>$query) {
                fwrite($fp, "* ".sprintf(_('Query %d: %s'),$n+1,substr($query,0,60).'...')."\n");
                if(isset($errors[$n+1])) {
                    fwrite($fp, "  * ".$errors[$n+1]."\n");
                }
                if(isset($results[$n+1])) {
                    fwrite($fp, "  * ".sprintf(_('Affected Rows: %d'),$results[$n+1])."\n");
                }
            }
            fclose($fp);
        }
        $this->render('index',array('files'=>$this->_getFiles(),'queries'=>$queries,'errors'=>$errors,'results'=>$results));
    }
    // {{{ _getFiles
    private function _getFiles()
    {
        $path = Yii::app()->basePath.'/../sql';
        $d = dir($path);
        $files = array();
        while (false !== ($entry = $d->read())) {
            if(preg_match("/^db_change(.*)\.sql$/",$entry)) {
                $files[$entry] = array(
                    'file'=>$entry,
                    'filectime'=> filectime($path.'/'.$entry),
                    'log'=>''
                );
                if(file_exists($path.'/'.$entry.'.log'))
                    $files[$entry]['log'] = filectime($path.'/'.$entry.'.log');
            }
        }
        $d->close();
        krsort($files);
        return $files;
    } // }}} 
}
// {{{ SqlSplitter
/**
 * SqlSplitter 
 * 
 * @package 
 * @version $Id: DbChangeScriptsController.php 3 2011-08-31 07:46:15Z juppwerner@googlemail.com $
 * @copyright 2010 Adam OPEL GmbH
 * @author Joachim Werner <joachim.pt.werner@de.gm.com> 
 * @license (tbd)
 */
class SqlSplitter
{

	public $delimiter = ';';

	private $string;
	private $queries = array();
	private $queryLength;
	
	private $position = 0;
	
	public $ignoreLastQuery = false;
	
	public function __construct($_string = false)
	{
		if($_string)
		{
			$this->string = $_string;
		}
	}
	
	public function getPosition()
	{
		return $this->position;
	}

	public function split()
	{
		$state = 0;
		$delimiter = $this->delimiter;
		$delimiterLength = strlen($delimiter);

		/*
		 * states:
		 *
		 *     0 initial state
		 *
		 * > 100 strings
		 *
		 *   	100 string active
		 *
		 * > 200 comments
		 *
		 * 		200 single-line comments
		 * 		210 multi-line comments
		 */

		$chars = strlen($this->string);
		$start = 0;
		$length = 0;
		$lastQuote = 0;
		$prevChar = null;

		for($i = 0; $i <= $chars; $i++)
		{

			if($i < $chars)
			{
				$char = $this->string{$i};
			}
			else
			{
				$char = null;
			}
			if($i < $chars - 1)
			{
				$nextChar = $this->string{$i+1};
			}
			else
			{
				$nextChar = null;
			}

			/*
			 * Comments
			 */
			
			// Only look for comments when not in a string
			if($state != 100)
			{
				if($i > 1 && $this->string{$i-2} . $this->string{$i-1} . $char == '-- ')
					$state = 200;
	
				if($prevChar . $char == '/*')
					$state = 210;
	
				if($state == 210 && $prevChar . $char == '*/')
				{
					$state = 0;
					#$start = $i;
				}
	
				if($state == 200 && $char == "\n")
				{
					$state = 0;
					#$start = $i;
				}
				
			}
			
			// Only look for strings when not in a comment
			if($char == '\'' && $state < 200)
			{
				#var_dump($state);

				// STRING start
				if($state == 0)
				{
					$state = 100;
					$lastQuote = $i+1;
				}

				elseif($state == 100)
				{

					$stringPart = substr($this->string, $lastQuote, $i-$lastQuote);

					// No backslash in string, skip testing
					if(!strpos($stringPart, '\\'))
					{
						$state = 0;
					}

					else
					{

						$backSlashCount = 0;
						for($j = strlen($stringPart)-1; $j >= 0; $j--)
						{
							if($stringPart{$j} == '\\')
								$backSlashCount++;
							else
								break;

						}

						if($backSlashCount % 2 == 0)
							$state = 0;
					}

				}

			}

			if($state == 0 &&
				(
					($char == $delimiter{0} &&
						(strlen($delimiter) == 1 ||
						$nextChar == $delimiter{1}))
					|| ($i == $chars && $this->ignoreLastQuery == false && count($this->queries) == 0)
				)
			)
			{
				
				$query = trim(substr($this->string, $start, $i-$start));
				#echo "found query: " . $query . "<br/>";

				if($query) 
				{
					$this->queries[] = $query;
					$this->queryLength[] = $i - $start + strlen($delimiter);
					
					$this->position = $i;
					
				}

				$start = $i+1;

				if($delimiterLength == 2 && $nextChar == $delimiter{1})
					$start++;

			}

			#echo $i . "\t" . '<b>' . $char . "</b>" . "\t" . (string)$state . '<br/>';

			$prevChar = $char;

		}

	}
	
	public function getQueries($_string = false)
	{
		if($_string)
		{
			$this->string = $_string;
			$this->position = 0;
			$this->startPositions = array();
			$this->queries = array();
		}
		
		if(!$this->queries)
			$this->split();

		return $this->queries;
	}

	public function getQueryLength($_i)
	{
		if(isset($this->queryLength[$_i]))
		{
			return $this->queryLength[$_i];
		}
		else
		{
			return 0;
		}
			
	}
	
} // }}}

/* vim:set ai sw=4 sts=4 et fdm=marker fdc=4: */ 
?>
